IF EXISTS (
    SELECT
      1
    FROM sysobjects
    WHERE id = OBJECT_ID('sync_ClientPro_Card_loss')
      AND type IN ('P', 'PC')
  )
  DROP PROCEDURE sync_ClientPro_Card_loss

GO

create  procedure [dbo].[sync_ClientPro_Card_loss] 	--挂失处理过程
(
@user_serial bigint,		--人员序号
@card_hao varchar(20),		--卡号
@bz nvarchar(50),			--摘要
@lx int = 0,					--过程类型（停用）
@ip varchar(20),			--ip
@gly_no nvarchar(50),		--管理员
@reg_serial varchar(50)		--企业编号
)
as
set nocount on

---
declare @card_serial char(8)	--卡顺序号
declare @card_lx int			--卡类型
declare @card_type int			--卡状态
declare @loss_count int			--挂失次数

declare @card_hao_virtual varchar(20)  --虚拟卡号
declare @card_serial_virtual varchar(20)  --虚拟卡逻辑号

--获取虚拟卡信息
select @card_serial_virtual=card_serial,@card_hao_virtual=card_hao
from dt_card
where user_serial=@user_serial and isnull(is_virtual_card ,0)=1

	select @card_serial=card_serial,@card_type=card_type,@loss_count=isnull(loss_count,0)+1,
	@card_lx=card_lx
	from dt_card
	where user_serial=@user_serial and card_hao=@card_hao

	--1.人员不存在
	if (select count(1) from dt_user where user_serial=@user_serial)=0
	begin

		select 2
		print '人员不存在!'

		return

	end

	--2.卡号状态不正常
	if(@card_type=1 or @card_type=2)	--挂失状态判断
	begin

		select 3	--卡号状态不正常 无法挂失
			print '卡号状态不正常,无法挂失,卡号:'+@card_hao
		return

	end

	begin transaction

		--1.更新卡户状态
		update dt_ac_card
		set ac_state=1
		where user_serial=@user_serial
		and card_serial=@card_serial

		--2.更新卡号表状态
		update dt_card
		set card_type=1,sj=getdate(),card_bz=@bz,loss_count=@loss_count
		where user_serial=@user_serial
		and card_serial=@card_serial

		--3.更新人员账户表
		update dt_user
		set user_card=cast(user_serial as varchar(50))
		where user_serial=@user_serial


		--4.更新dt_ac_link表
		update dt_ac_link
		set card_hao=@card_hao_virtual,card_serial=@card_serial_virtual
		where user_serial=@user_serial

		--4.删除卡号转换表
		delete a from dt_card_user a,dt_card b
		where a.Parent_xh=b.xh
		and b.user_serial=@user_serial
		and b.card_serial=@card_serial

		--5.生成增量日志20151021

			insert into wt_public(lx,log_type,is_all,user_serial,card_serial,log_sj,log_ip,gly_no,new_number,old_number)
			values(8,6,0,@user_serial,@card_serial,getdate(),@ip,@gly_no,0,0)
		--insert into WT_USER_UP(lx,log_type,card_serial,user_serial,log_sj,log_ip,gly_no)
		--values(4,1,@card_Serial,@user_serial,getdate(),@ip,@gly_no)

		--6.生成卡挂失日志
		insert into WT_CARD_LOG(
		lx,log_type,log_state,user_serial,card_old,card_new,
		log_xh,log_sj,log_ip,gly_no,log_erro,log_lx,regserial)
		VALUES(
		1,0,0,@user_serial,@card_hao,@card_serial,
		@card_lx,getdate(),@ip,@gly_no,0,0,@reg_serial
		)

		--7.返回成功
		select 1

	commit transaction





GO

